 
SELECT BAN
,LU_ID
FROM
(SELECT C801.BAN
,C801.SITE_ID
  ,C800.EXTERNAL_LIVING_UNIT_ID  AS  LU_ID
 ,CSUM(1,C801.LAST_UPDATE_TS  DESC) LATEST_ONE
 FROM TELCO_UNREG_RETAIL_VIEWS.VCCC801_SITE C801
         ,TELCO_UNREG_RETAIL_VIEWS.VCCC806_ADDRESS C806  
        , TELCO_UNREG_RETAIL_VIEWS.VCCC800_ADDRESS_HISTORY C800
 WHERE C801.SITE_ID = C806.SITE_ID   
  AND C800.ADDRESS_ID = C806.ADDRESS_ID
  AND C801.SITE_TYPE_CD = 'CUST' 
  AND C801.SITE_ROLE_NM = 'SERVICE'
  AND C806.ADDRESS_FORMAT_CD = 'FSP'
  AND C801.BAN IS NOT NULL
  AND C801.BAN <> ' '
  AND C800.LATEST_RECORD_IND = 'Y'
  GROUP BY 1 ) BASE
  WHERE LATEST_ONE = 1
	 